//
//  DBManager.m
//  AffectionBee
//
//  Created by 王炯 on 16/8/23.
//  Copyright © 2016年 chivalrous. All rights reserved.
//

#import "DBManager.h"
#import "FMDB.h"



static FMDatabase *_db;

@implementation DBManager


#warning 在initialize方法内创建一个存放在缓存中的数据库(在类收到第一个消息之前做类初始化的操作)
+(void)initialize
{
    //(1)指定数据库存放的地址
    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"product.db"];
    
    //(2)根据路径来创建一个数据库文件
    _db = [FMDatabase databaseWithPath:path];
    
    //(3)创建表(填写创建表的SQL语句)
    if ([_db open])
    {
        
        //创建表之前先把存在的表删除掉
        BOOL dropTable = [_db executeUpdate:@"DROP TABLE if exists 't_product'"];
        
        if (dropTable)
        {
            //NSLog(@"将表删除掉成功");
        }
        else
        {
            NSLog(@"将表删除掉失败");
        }
        
        
        //创建一个新的表
        BOOL result = [_db executeUpdate:@"CREATE TABLE if not exists 't_product' ('pNo' integer primary key autoincrement default 0,'pID' text,'pNumber' text,'pBuyNumber' text,'pName' text,'pPrice' text)"];
        
        if (result)
        {
            //NSLog(@"创建商品数据库表格成功");
        }
        else
        {
            NSLog(@"创建商品数据库表格失败");
        }
    }
}


//(1)增
+(void)AddData:(ProductModel *)model
{
    //填写增加数据的SQL语句
    BOOL addResult = [_db executeUpdate:[NSString stringWithFormat:@"insert into t_product ('pID','pNumber','pBuyNumber','pName','pPrice') values ('%@','%@','%@','%@','%@')",model.productId,model.number,@0,model.name,model.partner_price]];
    
    if (addResult)
    {
        //NSLog(@"添加数据成功");
    }
    else
    {
        NSLog(@"添加数据失败");
    }
}

//(2)删
+(void)DeleteData:(ProductModel *)model
{
    //填写删除数据的SQL语句
    BOOL result = [_db executeUpdate:[NSString stringWithFormat:@"delete from t_product where pID = %@",model.productId]];
    
    if (result)
    {
        //NSLog(@"删除数据成功");
    }
    else
    {
        NSLog(@"删除数据失败");
    }
}




//(3)改(只修改库存量和商品购买量)
+(void)UpdateDataWithModel:(ProductModel *)model
{
    BOOL result = [_db executeUpdate:[NSString stringWithFormat:@"update t_product set pNumber = '%@', pBuyNumber = '%@' where pID = %@",model.number,model.hasBuyNum,model.productId]];
    
    if (result)
    {
        //NSLog(@"更新数据成功");
    }
    else
    {
        NSLog(@"更新数据失败");
    }
}
//(4)根据产品的ID来获取当前商品的库存量和已经购买量
+(void)SearchDataWithModel:(ProductModel *)model
{
    //1-填写查询的SQL语句
    NSString *selectSql = [NSString stringWithFormat:@"select * from t_product where pID = %@",model.productId];
    
    //2-声明FMResultSet属性
    FMResultSet *set = [_db executeQuery:selectSql];
    
    //3-遍历查找并返回需要的数据
    while ([set next])
    {
        //取该行的每一行数据
        //(1)商品的总库存量
        NSString *storeNum = [set stringForColumn:@"pNumber"];
        
        //(2)商品已经的购买量
        NSString *hasBuyNum = [set stringForColumn:@"pBuyNumber"];
        
        
        //(3)更新model
        model.number = storeNum;
        
        model.hasBuyNum = hasBuyNum;
        
    }
}

//(5)清空表中的所有数据
+(void)ClearTableData
{
    //填写删除数据的SQL语句
    BOOL result = [_db executeUpdate:@"delete from t_product"];
    
    if (result)
    {
        //NSLog(@"清空数据成功");
    }
    else
    {
        NSLog(@"清空数据失败");
    }
}


//(6)按照购买数来查找数据库中数据
+(NSArray <ProductModel *> *)SearchDataWithHasBuyNum
{
    //1-填写查找的SQL语句
    NSString *selectSQL = @"select * from t_product where pBuyNumber != '0'";
    
    //2-声明FMResultSet属性
    FMResultSet *set = [_db executeQuery:selectSQL];
    
    NSMutableArray<ProductModel *> *mModelArray = [NSMutableArray array];
    
    
    //3-遍历查找并返回需要的数据
    while ([set next])
    {
        //取该行的每一行数据
        //(1)所购买商品的ID
        NSString *productID = [set stringForColumn:@"pID"];
        
        //(2)所购买商品的数量
        NSString *productBuyNum = [set stringForColumn:@"pBuyNumber"];
        
        //(3)所购买商品的商品名
        NSString *productName = [set stringForColumn:@"pName"];
        
        //(4)所购买商品的商品价格
        NSString *productPrice = [set stringForColumn:@"pPrice"];
        
        //(3)利用商品的ID和商品的数量创建一个model
        ProductModel *model = [ProductModel new];
        
        model.productId = productID;
        
        model.hasBuyNum = productBuyNum;
        
        model.name = productName;
        
        model.partner_price = productPrice;
        
        
        [mModelArray addObject:model];
    }
    
    return mModelArray.copy;
}



@end
